from rest_framework.response import Response
from rest_framework.decorators import api_view
import os
import pandas as pd
from sqlalchemy import create_engine
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
from deviceinfo import models
from django.http import StreamingHttpResponse
import ast
import json

mysql_engine = create_engine('mysql+mysqldb://user1:Beaconuser1!@10.129.7.200:3306/db_st?charset=utf8')


@api_view(http_method_names=['GET'])
def health(request):
    content = {}
    data = {'status': 'UP'}
    content['payload'] = {"data", json.dumps(data)}
    content['status'] = 1
    content['errmsg'] = ""
    return Response(data)


@api_view(http_method_names=["POST"])
def upload(request):
    if request.method == 'POST':
        filename = request.FILES.get("filename", None)
        if filename:
            # 判断当前目录下是否有excel这个子目录没有的话就创建（用来存放excel表）
            path = os.getcwd() + '\\' + 'excel'
            if not os.path.exists(path):
                os.mkdir(path)
            # path_file = path + '\\' + str(filename)
            path_file = path + '\\' + "resume.xlsx"
            if os.path.exists(path_file):
                os.remove(path_file)

            with open(path_file, 'wb') as w_excel:
                w_excel.write(filename.read())

            df = pd.read_excel(filename, skiprows=3)
            df = df.drop([0])
            df['設備類型'] = df['設備類型'].fillna(method='pad')

            # excel中的列名
            columns_Chinese = df.columns.values.tolist()

            # 刪除excel中的這2列
            del_columns = ['序號 ', '公式用隱藏項']
            for i in del_columns:
                columns_Chinese.remove(i)
            df = df.drop(columns=['序號 ', '公式用隱藏項'])

            # 列名對應的英文名
            columns_English = [
                'device_type', 'sub_group', 'site', 'product_department', 'cost_code', 'group_name', 'device_number',
                'device_name', 'device_brand', 'device_model', 'serial_number', 'origin_place', 'manufacture_date',
                'entry_date', 'acceptance_date', 'use_year', 'storage_location', 'regulatory_number', 'customs_number',
                'property_number', 'assets_manager', 'power', 'voltage', 'volume', 'weight', 'air_pressure',
                'air_consumption', 'system', 'speed_rated_speed', 'knife_handle_model', 'ip', 'nc_battery_brand',
                'nc_battery_model', 'driver_battery_brand', 'driver_battery_model', 'auxiliary_device_name',
                'auxiliary_device_brand', 'auxiliary_device_model', 'auxiliary_device_serial_number', 'owner',
                'maintenance_department_name', 'maintenance_department_code', 'maintenance_chief',
                'maintenance_team_leader', 'usage_status', 'remarks', 'hz_device_number'
            ]

            columns_dict = dict(zip(columns_Chinese, columns_English))

            df = df.rename(columns=columns_dict)
            db_conn = mysql_engine.connect()
            sql = "truncate table tb_device_info"
            db_conn.execute(sql)
            df.to_sql("tb_device_info", mysql_engine, if_exists='append', index=False)
        else:
            return Response("nothings is uploaded")
        return Response(status=201)


@api_view(http_method_names=["GET"])
def listing(request):
    contact_list = models.TbDeviceInfo.objects.all()
    # 對對象進行分頁每頁顯示50條
    paginator = Paginator(contact_list, 50)
    # 獲取對象的縂條數
    totalCount = paginator.count
    # 獲取對象的縂頁數
    numPages = paginator.num_pages
    page = request.GET.get('page')
    try:
        contacts = paginator.page(page)
    except PageNotAnInteger:
        contacts = paginator.page(1)
    except EmptyPage:
        contacts = paginator.page(paginator.num_pages)
    tableData = contacts.object_list.all().values()
    content = dict(payload={"tableData": tableData}, totalCount=totalCount, numPages=numPages, status=0, errmsg="")
    return Response(content)


def file_download(request):
    def file_iterator(file_name, chunk_size=512):
        with open(file_name, 'rb') as f:
            while True:
                c = f.read(chunk_size)
                if c:
                    yield c
                else:
                    break

    the_file_name = os.getcwd() + '\\' + 'excel' + '\\' + 'resume.xlsx'
    response = StreamingHttpResponse(file_iterator(the_file_name))
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename="{0}"'.format("example.xlsx")
    return response


# 設備信息查詢(按條件查詢)
@api_view(http_method_names=['GET'])
def query(request):
    # 完整的參數列表
    """
    完整的參數列表如下,傳入參數至少傳入一項如：parm={"site":"SZLH"}
    :param request:
    parm = {
        "site"：xxx, "group_name":xxx, "device_brand":xxx, "device_model":xxx, "serial_number":xxx,
        "storage_location":xxx,"property_number":xxx, "assets_manger":xxx
    }
    :return:
    """
    all_parm = [
        "site", "group_name", "device_number", "device_brand", "device_model", "serial_number",
        "storage_location", "property_number", "assets_manger"
    ]
    parm = request.GET.get("parm", None)
    if parm:
        try:
            parm = ast.literal_eval(parm)
        except:
            content = {"errmsg": "The format passed in the parameters are incorrect,illegal dictionary", "status": 4000}
            return Response(content)
        for i in parm:
            if i not in all_parm:
                content = {"status": 4001, "errmsg": 'WRONG_PARAMS, illegal parameter {0}'.format(i)}
                return Response(content)
        value = models.TbDeviceInfo.objects.filter(**parm).values()
        content = dict(payload={"tableData": value}, status=200)
    return Response(content)


# 在綫修改數據
@api_view(http_method_names=['GET'])
def edit(request):
    """
    參數列表如下必填參數為id，其他的至少填一個例如parm={"id":2,"site":"NanLing"}
    :param request:
    parm = {
        "id", "device_type", "sub_group", "site", "product_department", "cost_code", "group_name", "device_number",
        "device_name", "device_brand", "device_model", "serial_number", "origin_place", "manufacture_date", "entry_date",
        "acceptance_date", "use_year", "storage_location", "regulatory_number", "customs_number", "property_number",
        "assets_manager", "power", "voltage", "volume", "weight", "air_pressure", "air_consumption", "system",
        "speed_rated_speed", "knife_handle_model", "ip", "nc_battery_brand", "nc_battery_model", "driver_battery_brand",
        "driver_battery_model", "auxiliary_device_name", "auxiliary_device_brand", "auxiliary_device_model",
        "auxiliary_device_serial_number", "owner", "maintenance_department_name", "maintenance_department_code",
        "maintenance_chief", "maintenance_team_leader", "usage_status", "remarks", "hz_device_number"
    }
    :return:
    """
    all_parm = [
        "id", "device_type", "sub_group", "site", "product_department", "cost_code", "group_name", "device_number",
        "device_name", "device_brand", "device_model", "serial_number", "origin_place", "manufacture_date",
        "entry_date",
        "acceptance_date", "use_year", "storage_location", "regulatory_number", "customs_number", "property_number",
        "assets_manager", "power", "voltage", "volume", "weight", "air_pressure", "air_consumption", "system",
        "speed_rated_speed", "knife_handle_model", "ip", "nc_battery_brand", "nc_battery_model", "driver_battery_brand",
        "driver_battery_model", "auxiliary_device_name", "auxiliary_device_brand", "auxiliary_device_model",
        "auxiliary_device_serial_number", "owner", "maintenance_department_name", "maintenance_department_code",
        "maintenance_chief", "maintenance_team_leader", "usage_status", "remarks", "hz_device_number"
    ]
    parm = request.GET.get("parm", None)
    if parm:
        try:
            parm = ast.literal_eval(parm)
        except:
            content = {"errmsg": "The format passed in the parameters are incorrect,illegal dictionary", "status": 4000}
            return Response(content)
        for i in parm:
            if i not in all_parm:
                content = {"status": 4001, "errmsg": 'WRONG_PARAMS, illegal parameter {0}'.format(i)}
                return Response(content)
        id = parm.pop("id")
        models.TbDeviceInfo.objects.filter(id=id).update(**parm)
    return Response(status=200)


# 模糊匹配輸入框中的字符，讓交互更方便
@api_view(http_method_names=['GET'])
def match_attr(request):
    # 完整的參數列表
    """
    完整的參數列表如下,傳入參數只能一項如：parm={"site":"SZLH"}
    :param request:
    parm = {
        "site"：xxx, "group_name":xxx, "device_brand":xxx, "device_model":xxx, "serial_number":xxx,
        "storage_location":xxx,"property_number":xxx, "assets_manger":xxx
    }
    :return:
    """
    all_parm = [
        "site", "group_name", "device_number", "device_brand", "device_model", "serial_number",
        "storage_location", "property_number", "assets_manger"
    ]
    parm = request.GET.get("parm", None)
    if parm:
        try:
            parm = ast.literal_eval(parm)
        except:
            content = {"errmsg": "The format passed in the parameters are incorrect,illegal dictionary", "status": 4000}
            return Response(content)
        if len(parm) != 1:
            content = {"errmsg": "only 1 parameters is needed, but given {0}".format(len(parm)), "status": 4000}
            return Response(content)
        for i in parm:
            if i not in all_parm:
                content = {"status": 4001, "errmsg": 'WRONG_PARAMS, illegal parameter {0}'.format(i)}
                return Response(content)
        key = list(parm)[0]
        value = list(parm.values())[0]
        attrs = models.TbDeviceInfo.objects.filter(**{key+"__icontains": value}).values(key).distinct()
        attrs_list = [list(i.values())[0] for i in attrs]
        content = {"payload": attrs_list, "status": 0}
    return Response(content)
